-- @owner: lvlintao666
-- @date: 2024/05/13
-- @testpoint: 在start with ... connect by语句中使用prior递归查询
--step1:创建间隔分区表，插入数据  expect:建表成功，插入数据成功
drop table if exists t_prior_0002;
create table t_prior_0002
(pid int,
id int,
name varchar(100),
"int1" tinyint,
"int2" smallint,
"int4" integer,
"int8" bigint,
"float4" float4,
"float8" float8,
"numeric" decimal(20, 6),
"bit4" bit(4),
"boolean" boolean,
"date" date,
"time" time,
"timetz" timetz,
"timestamp" timestamp,
"timestamptz" timestamptz,
"char" char(100),
"varchar" varchar(100),
"blob" blob,
"text" text
);

insert into t_prior_0002 values (0,1,'node1',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0002 values (1,2,'node2',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0002 values (1,3,'node3',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0002 values (2,4,'node4',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0002 values (2,5,'node5',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0002 values (3,6,'node6',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');
insert into t_prior_0002 values (3,7,'node7',1,1,1,1,1,1,1,b'1111',true,'2023-02-05','19:00:00','19:00:00-08','2023-02-05 19:00:00','2023-02-05 19:00:00-08','1.23a','1.23a','123abc','1.23a');

--step2 在start with ... connect by语句中使用prior查询出正确结果;expect:成功
select prior name as father_name, prior "int1" as father_int1, name as current_name, "int1" as current_int1 from t_prior_0002 start with id = 2 connect by prior id = pid order by name ASC;
select prior name as father_name, prior "int1" as father_int1, name as current_name, "int1" as current_int1 from t_prior_0002 start with id = 2 connect by prior pid = id;
select prior name as father_name, prior "int1" as father_int1, name as current_name, "int1" as current_int1 from t_prior_0002 start with pid = 2 connect by prior pid = id;
select prior name as father_name, prior "int1" as father_int1, name as current_name, "int1" as current_int1 from t_prior_0002 start with pid = 2 connect by prior id = pid;

--step3 清理环境;expect:成功
drop table if exists t_prior_0002 cascade;